import pymysql as sql
from flask import jsonify
import datetime
import userMgmt.userLink.userRouter as userRouter

connect = sql.Connect(
    host='rm-bp131vjnd9b99vmuq2o.mysql.rds.aliyuncs.com',
    port=3306,
    user='lin_432718',
    passwd='Lin817818',
    db='rail_traffic',
    charset='utf8'
)
cursor = connect.cursor()

# 查询所有风险表
def searchFromData():
    sql = "SELECT risk_id, name, component_name FROM riskpoint, component " \
          "WHERE riskpoint.component_id = component.component_id ORDER BY risk_id ASC"
    cursor.execute(sql)

    attribute = ['risk_id', 'name', 'component_name']
    l = []
    for item in cursor.fetchall():
        dic = dict(map(lambda x, y: [x, y], attribute, item))
        l.append(dic)
    # print(l)
    return jsonify(l)

# 模糊查询表
def searchSpecData(word):
    sql = "SELECT risk_id, name, component_id FROM riskpoint WHERE name LIKE '%" + word + "%'"
    # print(sql)
    cursor.execute(sql)

    attribute = ['risk_id', 'name', 'component_id']
    l = []
    for item in cursor.fetchall():
        dic = dict(map(lambda x, y: [x, y], attribute, item))
        l.append(dic)
    # print(l)
    return jsonify(l)

# 新增风险项
def addData(cpnt_name, name):
    sql = "SELECT component_id from component WHERE component_name = '" + cpnt_name + "'"
    cursor.execute(sql)
    cpnt_id = cursor.fetchall()
    # print(cpnt_id)
    id = getnewnumber()
    sql = "INSERT INTO riskpoint(risk_id, name, component_id) VALUES ('" + id + "', '" + name + "', '" + cpnt_id[0][0] + "')"
    # print(sql)
    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return jsonify(0)
    else:
        return jsonify(1)

# 给风险项寻找最大编号
def getnewnumber():
    sql = "select max(risk_id) from riskpoint"
    cursor.execute(sql)
    data = cursor.fetchone()
    # print(data)
    number = int(data[0]) + 1
    #  print(number)
    str = "%03d" % number
    # print(str)

    return str


# 展示某一个风险的具体信息
def selectDet(risk_id):
    sql = "select riskpoint.name, riskpoint.describe, component_name, checktime_1, checktime_2, gap " \
          "FROM riskpoint, component WHERE risk_id = '" + risk_id + "' AND riskpoint.component_id = component.component_id"
    # print(sql)

    cursor.execute(sql)

    attribute = ['risk_name', 'describe', 'cpnt_name', 'time1', 'time2', 'gap']
    l = []
    for item in cursor.fetchall():
        dic = dict(map(lambda x, y: [x, y], attribute, item))
        if dic['time1'] is not None:
            dic['time1'] = dic['time1'].strftime("%Y-%m-%d")
            dic['time2'] = dic['time2'].strftime("%Y-%m-%d")
        l.append(dic)

    print(userRouter.admin)
    return jsonify(l)


# 修改风险的详细信息
def modifyD(risk_id, name, cpnt_name, describe):

    sql ="SELECT component_id FROM component WHERE component_name = '" + cpnt_name + "'"
    cursor.execute(sql)
    cpnt_id = cursor.fetchall()[0][0]

    sql = "update riskpoint set component_id='" + cpnt_id + "', riskpoint.name='" + name + \
          "', riskpoint.describe= '" + describe + "' WHERE risk_id='" + risk_id + "'"
    print(sql)
    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return jsonify(0)
    else:
        return jsonify(1)

def handleDel(risk_id):

    sql = "Delete from riskpoint WHERE risk_id = '" + risk_id + "'"
    print(sql)
    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return jsonify(0)
    else:
        return jsonify(1)


